02. RDBMS

Relational Database

A relational database is a collection of data items with pre-defined relationships between them. These items are organized as a set of tables with columns and rows. Each row in a table can have a unique identifier called a primary key, and rows among multiple tables can be made related using foreign keys. Foreign keys are also known as referential integrity keys.

SQL

Structured Query Language is the primary interface used to communicate with Relational Databases.

Transactions

A database transaction is one or more SQL statements that are executed as a sequence of operations that form a single logical unit of work. Transactions provide an "all-or-nothing" proposition, meaning that the entire transaction must complete as a single unit and be written to the database or none of the individual components of the transaction should go through.

Atomicity

Atomicity requires that either transaction as a whole be successfully executed or if a part of the transaction fails, then the entire transaction be invalidated.

Consistency

Consistency mandates the data written to the database as part of the transaction must adhere to all defined rules and referential integrity constraints.

Isolation

Transactions are often executed concurrently (e.g., reading and writing to multiple tables at the same time). Isolation ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially. Isolation is the main goal of concurrency control, the effects of an incomplete transaction might not even be visible to other transactions.

Durability

Durability requires that all of the changes made to the database be permanent once a transaction is successfully completed.

SQL

Structured Query Language is the primary interface used to communicate with Relational Databases.

SQL is a declarative domain specific language that is specifically designed for managing data held in a relational database management system. SQL is declarative since it only describes the desired results without explicitly listing commands or steps that must be performed.

SQL statements can be broadly classified into

• DDL
• DML

DDL

Data Description Language is used for defining database schemas. DDL statements create, modify, and remove database objects such as tables, indexes, and users. Common DDL statements are CREATE, ALTER and DROP.

DML

Data Manipulation Language is used for adding (inserting), deleting, and modifying (updating) data in a relational database. Common DML statements are INSERT, UPDATE and DELETE.